﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using DTO;


namespace DAO
{
    public class PhieuNhapDAO
    {
        public List<PhieuNhapDTO> LayDSPhieuNhap()
        {
            string sql = "SELECT MaPN,NgayNhap,MaDH,MaNV,GhiChu FROM PhieuNhap ";
            OleDbConnection conn = DataProvider.ConnectDB();
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            OleDbDataReader reader = cmd.ExecuteReader();

            List<PhieuNhapDTO> danhsach = new List<PhieuNhapDTO>();

            while (reader.Read())
            {
                PhieuNhapDTO pnDTO = new PhieuNhapDTO();
                pnDTO.MaPN = reader.GetInt32(0);
                pnDTO.NgayNhap = reader.GetDateTime(1);
                pnDTO.MaDH = reader.GetInt32(2);
                pnDTO.MaNV = reader.GetInt32(3);
                pnDTO.GhiChu = reader.GetString(4);
                danhsach.Add(pnDTO);
            }
            conn.Close();
            return danhsach;
        }

        public PhieuNhapDTO LayPhieu(int maPhieu)
        {
            string sql = "SELECT MaPN,NgayNhap,MaDH,MaNV,GhiChu  FROM PhieuNhap WHERE MaPN = " + maPhieu;
            OleDbConnection conn = DataProvider.ConnectDB();
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            OleDbDataReader reader = cmd.ExecuteReader();
            PhieuNhapDTO pn = null;
            while (reader.Read())
            {
                pn.MaPN = reader.GetInt32(0);
                pn.NgayNhap = reader.GetDateTime(1);
                pn.MaDH = reader.GetInt32(2);
                pn.MaNV = reader.GetInt32(3);
                pn.GhiChu = reader.GetString(4);
            }
            conn.Close();
            return pn;
        }

        public int LayMaLonNhat()
        {
            string sql = "SELECT Max(MaPN) FROM PhieuNhap ";
            OleDbConnection conn = DataProvider.ConnectDB();
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            int kq = int.Parse(cmd.ExecuteScalar().ToString());
            conn.Close();
            return kq;
        }

        public List<PhieuNhapDTO> LayDSPNTheoNgay(DateTime From, DateTime To)
        {
            string sql = " SELECT MaPN,NgayNhap,MaDH,MaNV,GhiChu FROM PhieuNhap ";
            sql = sql + " WHERE NgayNhap BETWEEN @F AND @T";

            OleDbConnection conn = DataProvider.ConnectDB();
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            
            OleDbParameter para1 = cmd.Parameters.Add("@F", OleDbType.DBDate);
            para1.Value = From;
            OleDbParameter para2 = cmd.Parameters.Add("@T", OleDbType.DBDate);
            para2.Value = To;

            OleDbDataReader reader = cmd.ExecuteReader();
            List<PhieuNhapDTO> danhsach = new List<PhieuNhapDTO>();

            while (reader.Read())
            {
                PhieuNhapDTO pnDTO = new PhieuNhapDTO();
                pnDTO.MaPN = reader.GetInt32(0);
                pnDTO.NgayNhap = reader.GetDateTime(1);
                pnDTO.MaDH = reader.GetInt32(2);
                pnDTO.MaNV = reader.GetInt32(3);
                pnDTO.GhiChu = reader.GetString(4);
                danhsach.Add(pnDTO);
            }
            conn.Close();
            return danhsach;
        }

        public int XoaPhieuNhap(int maPN)
        {
            string sql1 = "DELETE FROM PhieuNhap WHERE MaPN = " + maPN;
            OleDbConnection conn = DataProvider.ConnectDB();
            OleDbCommand cmd1 = new OleDbCommand(sql1, conn);
            int kq = cmd1.ExecuteNonQuery();
            conn.Close();
            return kq;
        }

        public int ThemPhieuNhap(PhieuNhapDTO pnDTO)
        {
            string sql = " INSERT INTO PhieuNhap(MaPN,NgayNhap,MaDH,MaNV,GhiChu) VALUES(@MaPN,@NgayNhap,@MaDH,@MaNV,@GhiChu) ";
            OleDbConnection conn = DataProvider.ConnectDB();
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            OleDbParameter para;
            para = cmd.Parameters.Add("@MaPN", OleDbType.Integer);
            para.Value = pnDTO.MaPN;
            para = cmd.Parameters.Add("@NgayNhap", OleDbType.DBDate);
            para.Value = pnDTO.NgayNhap;
            para = cmd.Parameters.Add("@MaDH", OleDbType.Integer);
            para.Value = pnDTO.MaDH;
            para = cmd.Parameters.Add("@MaNV", OleDbType.Integer);
            para.Value = pnDTO.MaNV;
            para = cmd.Parameters.Add("@GhiChu", OleDbType.VarChar);
            para.Value = pnDTO.GhiChu;
            int kq = cmd.ExecuteNonQuery();
            conn.Close();
            if (kq == 1)
                return 1;
            return 0;
            
        }


        public int SuaPhieuNhap(PhieuNhapDTO pnDTO)
        {
            string sql = " UPDATE PhieuNhap SET NgayNhap = @NgayNhap, MaDH = @MaDH , MaNV = @MaNV, GhiChu = @GhiChu WHERE MaPN = " + pnDTO.MaPN;
            OleDbConnection conn = DataProvider.ConnectDB();
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            OleDbParameter para;
            //para = cmd.Parameters.Add("@MaPN", OleDbType.Integer);
            //para.Value = pnDTO.MaPN;
            para = cmd.Parameters.Add("@NgayNhap", OleDbType.DBDate);
            para.Value = pnDTO.NgayNhap;
            para = cmd.Parameters.Add("@MaDH", OleDbType.Integer);
            para.Value = pnDTO.MaDH;
            para = cmd.Parameters.Add("@MaNV", OleDbType.Integer);
            para.Value = pnDTO.MaNV;
            para = cmd.Parameters.Add("@GhiChu", OleDbType.VarChar);
            para.Value = pnDTO.GhiChu;

            int kq = cmd.ExecuteNonQuery();
            conn.Close();
            if (kq == 1)
                return 1;
            return 0;

        }
    }
}
